import json
import mysql.connector
from mysql.connector import Error

def generate_sql():
    # 数据库配置
    MYSQL_USER = 'psychology_test'
    MYSQL_PASSWORD = '123456'
    MYSQL_HOST = '114.55.93.104'
    MYSQL_PORT = '3306'
    MYSQL_DATABASE = 'psychology_test'
    
    try:
        # 连接数据库
        connection = mysql.connector.connect(
            user=MYSQL_USER,
            password=MYSQL_PASSWORD,
            host=MYSQL_HOST,
            port=MYSQL_PORT,
            database=MYSQL_DATABASE
        )
        
        if connection.is_connected():
            cursor = connection.cursor()
            
            # 读取data.json文件
            with open('data.json', 'r', encoding='utf-8') as f:
                data = json.load(f)
            
            # 提取questions数据
            questions = data['data']
            
            # 插入数据
            sort_order = 1
            inserted_count = 0
            
            for question in questions:
                title = question['Title'].replace("'", "''")
                dim_val = question['DimVal'].replace("'", "''")
                
                # SQL插入语句
                sql = """
                INSERT INTO `questions` 
                (`assessment_id`, `title`, `content`, `image`, `question_type`, `sort_order`, `score`, `is_required`, `options`, `correct_answer`, `analysis`, `dimension`, `reverse_score`, `id`, `created_at`, `updated_at`) 
                VALUES 
                ('50', %s, '', '', 'SINGLE_CHOICE', %s, '1', '1', '[{"label": "确实是这样", "value": 5}, {"label": "可能是这样", "value": 4},{"label": "不同意也不反对", "value": 3}, {"label": "可能不是这样", "value": 2},{"label": "可能不是这样", "value": 1}]', '', '', %s, '0', NULL, '2025-08-31 15:44:04', '2025-08-31 15:44:04')
                """
                
                cursor.execute(sql, (title, sort_order, dim_val))
                print(title, sort_order, dim_val)
                inserted_count += 1
                sort_order += 1
            
            # 提交事务
            connection.commit()
            print(f"成功插入 {inserted_count} 条记录到数据库")
            
    except Error as e:
        print(f"数据库连接错误: {e}")
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("数据库连接已关闭")

if __name__ == '__main__':
    generate_sql()